package com.framework.boc.poi;

import com.framework.boc.error.CommonException;
import com.framework.boc.error.ErrorEnum;
import com.framework.boc.poi.annotation.Excel;
import com.framework.boc.util.Convert;
import com.framework.boc.util.DateUtils;
import com.framework.boc.util.ReflectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Excel操作工具类
 * @param <T>   导入/到处数据类型
 * @Autor       Leil
 * @Date        2020-7-15
 */
public class ExcelUtil<T> {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    private Workbook workbook;

    private Class<T> tClass;

    private List<T> list;

    public ExcelUtil(Class<T> tClass) {
        this.tClass = tClass;
    }


    /**
     * 导入Excel的第一个sheet
     * @param is    输入流
     * @return      导入后生成的数据集合
     */
    public List<T> importExcel(InputStream is) throws CommonException {
        return importExcel(null, is);
    }


    /**
     * 导入Excel的指定名称的sheet
     * @param sheetName     sheet名称
     * @param is            输入流
     * @return              导入后生成的数据集合
     */
    public List<T> importExcel(String sheetName, InputStream is) throws CommonException {
        try {
            this.workbook = WorkbookFactory.create(is);
        } catch (IOException e) {
            logger.error(e.getMessage());
            throw new CommonException(ErrorEnum.IO_ERROR);
        } catch (InvalidFormatException e) {
            logger.error(e.getMessage());
            throw new CommonException(ErrorEnum.FORMAT_ERROR);
        }
        Sheet sheet;
        if (StringUtils.isEmpty(sheetName)) {
            sheet = this.workbook.getSheetAt(0);
        } else {
            sheet = this.workbook.getSheet(sheetName);
        }
        if (sheet == null) {
            throw new CommonException(ErrorEnum.IO_ERROR, "文件sheet不存在");
        }
        if (sheet.getPhysicalNumberOfRows() <= 0)
            return null;
        return readSheet(sheet);
    }

    /**
     * 读取一个sheet
     * @param sheet     sheet对象
     * @return          读取后数据集合
     */
    private List<T> readSheet(Sheet sheet) throws CommonException {
        Map<Integer, Field> fieldsMap = new HashMap<>();
        this.processHeader(sheet.getRow(0), fieldsMap);
        try {
            this.processData(sheet, fieldsMap);
        } catch (Exception e) {
            logger.error(e.getMessage());
            throw new CommonException(ErrorEnum.UNKNOW_ERROR, "反射错误");
        }
        return list;
    }

    /**
     * 处理Excel头
     * @param row           Excel的header（第一行）
     * @param fieldsMap     属性字段--列序号map
     */
    private void processHeader(Row row, Map<Integer, Field> fieldsMap) {
        Field[] fields = tClass.getDeclaredFields();
        Map<String, Field> map = new HashMap<>();
        for (Field field : fields) {
            Excel attr = field.getAnnotation(Excel.class);
            if (attr != null) {
                map.put(attr.name(), field);
            }
        }
        int minColIx = row.getFirstCellNum();
        int maxColIx = row.getLastCellNum();
        for (int i = minColIx; i < maxColIx; i++) {
            Cell cell = row.getCell(i);
            if (cell == null) continue;
            String val = getCellValue(cell).toString();
            Field field = map.get(val);
            if(field != null) {
                fieldsMap.put(i, field);
            }
        }
    }

    /**
     * 处理Excel数据
     * @param sheet         工作表
     * @param fieldsMap     表索引---实体字段map
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    private void processData(Sheet sheet, Map<Integer, Field> fieldsMap) throws IllegalAccessException, InstantiationException {
        int rowCount = sheet.getPhysicalNumberOfRows();
        T entity = tClass.newInstance();
        this.list = new ArrayList<>();
        for (int i = 1; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            fieldsMap.entrySet().forEach(entry -> {
                Object val = this.getCellValue(row.getCell(entry.getKey()));
                Field field = entry.getValue();
                field.setAccessible(true);
                Class<?> fieldType = field.getType();

                if (String.class == fieldType) {
                    String s = Convert.toStr(val);
                    if (StringUtils.endsWith(s, ".0")) {
                        val = StringUtils.substringBefore(s, ".0");
                    } else {
                        String dateFormat = field.getAnnotation(Excel.class).dateFormat();
                        if (StringUtils.isNotEmpty(dateFormat)) {
                            val = DateUtils.parseDateToStr(dateFormat, (Date) val);
                        } else {
                            val = s;
                        }
                    }
                } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                    val = Convert.toInt(val);
                } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                    val = Convert.toLong(val);
                } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                    val = Convert.toDouble(val);
                } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                    val = Convert.toFloat(val);
                } else if (BigDecimal.class == fieldType) {
                    val = Convert.toBigDecimal(val);
                } else if (Date.class == fieldType) {
                    if (val instanceof String) {
                        val = DateUtils.parseDate(val);
                    } else if (val instanceof Double) {
                        val = DateUtil.getJavaDate((Double) val);
                    }
                }
                Excel attr = field.getAnnotation(Excel.class);
                String propertyName = field.getName();
                if (StringUtils.isNotEmpty(attr.targetAttr())) {
                    propertyName = field.getName() + "." + attr.targetAttr();
                } else if (StringUtils.isNotEmpty(attr.readConverterExp())) {
                    try {
                        val = reverseByExp(String.valueOf(val), attr.readConverterExp());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                ReflectUtils.invokeSetter(entity, propertyName, val);
            });
            this.list.add(entity);
        }
    }


    /**
     * 获取单元格的值
     * @param cell      单元格
     * @return          单元格对象
     */
    public Object getCellValue(Cell cell) {
        Object val = "";
        try {
            if (cell != null) {
                if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    } else {
                        if ((Double) val % 1 > 0) {
                            val = new DecimalFormat("0.00").format(val);
                        } else {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }

            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }


    /**
     * 反向解析值 男=0,女=1,未知=2
     *
     * @param propertyValue 参数值
     * @param converterExp  翻译注解
     * @return 解析后值
     * @throws Exception
     */
    public String reverseByExp(String propertyValue, String converterExp) throws Exception {
        String[] convertSource = converterExp.split(",");
        for (String item : convertSource) {
            String[] itemArray = item.split("=");
            if (itemArray[1].equals(propertyValue)) {
                return itemArray[0];
            }
        }
        return propertyValue;
    }


}
